---
title: SAIF.Application.Claim.Legal database tables
summary: Database tables used by this library with relationships and usage context.
status: draft
owner: SAIF Documentation Pipeline
last_reviewed: 2026-08-21
library: SAIF.Application.Claim.Legal
reference_type: embedded
---

# SAIF.Application.Claim.Legal database tables

This page catalogs the Oracle database tables accessed by this library. This library contains **zero `IBusinessCriteria` implementations**; all SQL cataloged here is application-layer SQL authored directly in `AppManager`-style classes, bypassing the business/`IBusinessCriteria` layer*. No SQL in this library uses an explicit `OWNER.TABLE_NAME` schema qualifier, so every table's schema is recorded as `[unqualified]**`.

## Table catalog

| Table | Database | Schema/Link | CRUD | Purpose | Primary business object |
| --- | --- | --- | --- | --- | --- |
| [POLICY](#policy) | Oracle | `[unqualified]**` | R | Distinct policy name lookup for attorney-assignment maintenance | `clsAttorneyTableManager` |
| [DURATION_CODE](#duration_code) | Oracle | `[unqualified]**` | R | Hearing/schedule duration code lookup | `clsAttorneyTableManager`, `clsHearingLocationManager` |
| [HEARING_LOCATION_CODE](#hearing_location_code) | Oracle | `[unqualified]**` | R | Hearing location code lookup | `clsAttorneyTableManager`, `clsHearingLocationManager` |
| [ATTORNEY_SCHEDULE_TYPE_CODE](#attorney_schedule_type_code) | Oracle | `[unqualified]**` | R | Attorney schedule type code lookup | `clsAttorneyTableManager` |
| [ADP_EMPLOYEE](#adp_employee) | Oracle | `[unqualified]**` | R | Employee source for adjuster and attorney name lookups | `clsAttorneyTableManager` |
| [ATTORNEY_PROFILE](#attorney_profile) | Oracle | `[unqualified]**` | R | Attorney profile records, incl. assigned secretary | `clsAttorneyTableManager`, `clsSummaryManager` |
| [LEGAL_CODE_TABLE](#legal_code_table) | Oracle | `[unqualified]**` | R | Generic maintenance-window code table | `clsAttorneyTableManager`, `clsLegalPersonManager` |
| [LEGAL_PERSON](#legal_person) | Oracle | `[unqualified]**` | R | Legal person master records (ALJs, board members, attorneys) | `clsAttorneyTableManager`, `clsOtherPartiesManager`, `clsUpdateCaseManager` |
| [LEGAL_PERSON_TYPE](#legal_person_type) | Oracle | `[unqualified]**` | R | Type/category classification for `LEGAL_PERSON` rows | `clsAttorneyTableManager`, `clsOtherPartiesManager`, `clsUpdateCaseManager` |
| [OTHER_FIRM_TYPE_CODE](#other_firm_type_code) | Oracle | `[unqualified]**` | R | Other-firm type code lookup | `clsLegalPersonManager` |
| [LEGAL_PERSON_TYPE_CODE](#legal_person_type_code) | Oracle | `[unqualified]**` | R | Legal person-type code lookup | `clsLegalPersonManager` |
| [OTHER_FIRM](#other_firm) | Oracle | `[unqualified]**` | R | Firm-level attorney/employer/insurer records | `clsOtherPartiesManager`, `clsUpdateCaseManager` |
| [APPEAL](#appeal) | Oracle | `[unqualified]**` | R | Case appeal records | `clsOutcomeManager` |
| [REVIEW_LEVEL_CODE](#review_level_code) | Oracle | `[unqualified]**` | R | Review/hearing level code lookup | `clsOutcomeManager` |
| [CASE_STATUS_CODE](#case_status_code) | Oracle | `[unqualified]**` | R | Case status code lookup, filtered by prior status history | `clsUpdateCaseManager` |
| [DUAL](#dual) | Oracle | N/A (pseudo-table) | R | Oracle pseudo-table for scalar function calls and placeholder rows | `clsScheduleManager`, `clsOtherPartiesManager`, `clsOutcomeManager` |

<!-- CRUD column lists applicable letters: C = Create/Insert, R = Read/Select, U = Update, D = Delete. -->

## POLICY

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Provides a distinct policy number/name lookup for the attorney-assignment maintenance screen. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `POLICY_NUM`, `POLICY_NAME` |
| **Sequence** | N/A |
| **Related tables** | None identified |
| **Business objects** | `clsAttorneyTableManager` (App class) |

### SQL referencing this table
- [SQL-008: Retrieve distinct policy name list](sql/SQL-008-policy-name-list.md)

---

## DURATION_CODE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Supplies the duration code list used by attorney-assignment and hearing-location maintenance screens. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `CODE`, `CODE_DESCR` |
| **Sequence** | N/A |
| **Related tables** | None identified |
| **Business objects** | `clsAttorneyTableManager`, `clsHearingLocationManager` (App classes) |

### SQL referencing this table
- [SQL-006: Retrieve hearing duration code list](sql/SQL-006-duration-code-list.md)
- [SQL-010: Retrieve duration code list (hearing location screen)](sql/SQL-010-duration-code-list-hearing-location.md)

---

## HEARING_LOCATION_CODE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Supplies the hearing location code list used by attorney-assignment and hearing-location maintenance screens. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `CODE`, `CODE_DESCR` |
| **Sequence** | N/A |
| **Related tables** | None identified |
| **Business objects** | `clsAttorneyTableManager`, `clsHearingLocationManager` (App classes) |

### Business rules referencing this table
- [BR-LGL-015: A hearing location cannot be deleted while in use](business-rules.md#br-lgl-015-a-hearing-location-cannot-be-deleted-while-in-use)*

### SQL referencing this table
- [SQL-007: Retrieve hearing location code list](sql/SQL-007-location-code-list.md)
- [SQL-011: Retrieve hearing location code list (hearing location screen)](sql/SQL-011-location-code-list-hearing-location.md)

---

## ATTORNEY_SCHEDULE_TYPE_CODE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Supplies the attorney schedule type code list for attorney-assignment maintenance. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `CODE`, `CODE_DESCR` |
| **Sequence** | N/A |
| **Related tables** | None identified |
| **Business objects** | `clsAttorneyTableManager` (App class) |

### SQL referencing this table
- [SQL-005: Retrieve attorney schedule type codes](sql/SQL-005-attorney-schedule-type-list.md)

---

## ADP_EMPLOYEE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Source of adjuster and attorney employee names; joined to `ATTORNEY_PROFILE` to resolve attorney display names. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `EMPLID`, `LAST_NAME`, `FIRST_NAME`, `PREFERRED_NAME`, `EMPL_STATUS`, `DESCR` |
| **Sequence** | N/A |
| **Related tables** | `ATTORNEY_PROFILE` |
| **Business objects** | `clsAttorneyTableManager` (App class) |

### SQL referencing this table
- [SQL-002: Retrieve claims adjuster code list](sql/SQL-002-adjuster-list.md)
- [SQL-004: Retrieve attorney list with names](sql/SQL-004-attorney-list.md)

---

## ATTORNEY_PROFILE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Attorney profile records, joined to `ADP_EMPLOYEE` to build attorney lists, and queried directly to resolve an assigned attorney's secretary employee ID. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `PROFILE_EMPLID`, `SECRETARY_EMPLID` |
| **Sequence** | N/A |
| **Related tables** | `ADP_EMPLOYEE` |
| **Business objects** | `clsAttorneyTableManager`, `clsSummaryManager` (App classes) |

### SQL referencing this table
- [SQL-004: Retrieve attorney list with names](sql/SQL-004-attorney-list.md)
- [SQL-021: Retrieve legal secretary employee ID by attorney](sql/SQL-021-get-legal-secretary-id.md)

---

## LEGAL_CODE_TABLE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Generic maintenance-window code table, filtered by `TABLE_MAINT_WIN_NAME` to drive attorney-assignment and person-maintenance code lookups. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `TABLE_NAME`, `TABLE_MAINT_WIN_NAME` |
| **Sequence** | N/A |
| **Related tables** | None identified |
| **Business objects** | `clsAttorneyTableManager`, `clsLegalPersonManager` (App classes) |

### SQL referencing this table
- [SQL-009: Retrieve attorney-assignment maintenance table list](sql/SQL-009-attorney-assignment-table-list.md)
- [SQL-014: Retrieve legal code tables for person maintenance](sql/SQL-014-legal-view-code-list.md)

---

## LEGAL_PERSON

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Legal person master records (ALJs, board members, attorneys), joined to `LEGAL_PERSON_TYPE` to build person/attorney selection lists across multiple maintenance screens. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `LEGAL_PERSON_SEQ_NUM`, `LAST_NAME`, `FIRST_NAME` |
| **Sequence** | N/A |
| **Related tables** | `LEGAL_PERSON_TYPE` |
| **Business objects** | `clsAttorneyTableManager`, `clsOtherPartiesManager`, `clsUpdateCaseManager` (App classes) |

### SQL referencing this table
- [SQL-003: Retrieve administrative law judge (ALJ) list](sql/SQL-003-alj-list.md)
- [SQL-017: Retrieve attorney person list for other parties](sql/SQL-017-other-parties-attorney-person-list.md)
- [SQL-023: Retrieve attorney person list for case update](sql/SQL-023-update-case-attorney-person-list.md)

---

## LEGAL_PERSON_TYPE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Type/category classification for `LEGAL_PERSON` rows (e.g. ALJ, BOARD, FJ, ATTY, OUTC), including expiration tracking. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `LEGAL_PERSON_SEQ_NUM`, `LEGAL_PERSON_TYPE_CODE`, `EXP_DATE` |
| **Sequence** | N/A |
| **Related tables** | `LEGAL_PERSON` |
| **Business objects** | `clsAttorneyTableManager`, `clsOtherPartiesManager`, `clsUpdateCaseManager` (App classes) |

### SQL referencing this table
- [SQL-003: Retrieve administrative law judge (ALJ) list](sql/SQL-003-alj-list.md)
- [SQL-017: Retrieve attorney person list for other parties](sql/SQL-017-other-parties-attorney-person-list.md)
- [SQL-023: Retrieve attorney person list for case update](sql/SQL-023-update-case-attorney-person-list.md)

---

## OTHER_FIRM_TYPE_CODE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Other-firm type code lookup for legal-person/firm maintenance. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `CODE`, `CODE_DESCR` |
| **Sequence** | N/A |
| **Related tables** | None identified |
| **Business objects** | `clsLegalPersonManager` (App class) |

### Business rules referencing this table
- [BR-LGL-016: A legal person cannot be deleted while in use](business-rules.md#br-lgl-016-a-legal-person-cannot-be-deleted-while-in-use)*

### SQL referencing this table
- [SQL-013: Retrieve other-firm type code list](sql/SQL-013-firm-type-list.md)

---

## LEGAL_PERSON_TYPE_CODE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Legal person-type code lookup for legal-person/firm maintenance. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `CODE`, `CODE_DESCR` |
| **Sequence** | N/A |
| **Related tables** | None identified |
| **Business objects** | `clsLegalPersonManager` (App class) |

### Business rules referencing this table
- [BR-LGL-016: A legal person cannot be deleted while in use](business-rules.md#br-lgl-016-a-legal-person-cannot-be-deleted-while-in-use)*

### SQL referencing this table
- [SQL-015: Retrieve legal person type code list](sql/SQL-015-person-type-list.md)

---

## OTHER_FIRM

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Firm-level attorney/employer/insurer records used as an alternative to `LEGAL_PERSON`/`LEGAL_PERSON_TYPE` for other-parties and update-case attorney lists. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `OTHER_FIRM_SEQ_NUM`, `FIRM_NAME`, `OTHER_FIRM_TYPE_CODE` |
| **Sequence** | N/A |
| **Related tables** | None (queried as a parallel, non-joined alternative to `LEGAL_PERSON`/`LEGAL_PERSON_TYPE`, not a foreign-key relationship)* |
| **Business objects** | `clsOtherPartiesManager`, `clsUpdateCaseManager` (App classes) |

### SQL referencing this table
- [SQL-016: Retrieve attorney firm list for other parties](sql/SQL-016-other-parties-attorney-list.md)
- [SQL-018: Retrieve employer/insurer firm list for other parties](sql/SQL-018-employer-type-list.md)
- [SQL-022: Retrieve attorney firm list for case update](sql/SQL-022-update-case-attorney-list.md)

---

## APPEAL

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Case appeal records, joined to `REVIEW_LEVEL_CODE` to build the appeal/hearing-level selection list for case outcome maintenance. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `APPEAL_SEQ_NUM`, `APPEAL_NUM`, `LEGAL_CASE_SEQ_NUM`, `REVIEW_LEVEL_CODE` |
| **Sequence** | N/A |
| **Related tables** | `REVIEW_LEVEL_CODE` |
| **Business objects** | `clsOutcomeManager` (App class) |

### SQL referencing this table
- [SQL-019: Retrieve appeal hearing level list by case](sql/SQL-019-appeal-num-level-list.md)

---

## REVIEW_LEVEL_CODE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Lookup of review/hearing level codes and descriptions, left-outer-joined from `APPEAL`. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `CODE`, `CODE_DESCR` |
| **Sequence** | N/A |
| **Related tables** | `APPEAL` |
| **Business objects** | `clsOutcomeManager` (App class) |

### SQL referencing this table
- [SQL-019: Retrieve appeal hearing level list by case](sql/SQL-019-appeal-num-level-list.md)

---

## CASE_STATUS_CODE

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | `[unqualified]**` |
| **Purpose** | Case status code lookup, filtered to a case's prior status history values, for the case-status maintenance dropdown. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | `CODE`, `CODE_DESCR` |
| **Sequence** | N/A |
| **Related tables** | `LEGAL_CASE` (owned by the referenced `SAIF.Business.Claim.Legal` library; not queried directly by this library — relationship inferred from the `_STATUS_CODE` naming convention, not a JOIN visible in this library's own SQL)* |
| **Business objects** | `clsUpdateCaseManager` (App class) |

### Business rules referencing this table
- [BR-LGL-009: Worker-attorney firm change creates a system audit case note](business-rules.md#br-lgl-009-worker-attorney-firm-change-creates-a-system-audit-case-note)*
- [BR-LGL-010: Worker attorney change creates a system audit case note](business-rules.md#br-lgl-010-worker-attorney-change-creates-a-system-audit-case-note)*
- [BR-LGL-012: Secondary consolidated cases mirror the primary case state](business-rules.md#br-lgl-012-secondary-consolidated-cases-mirror-the-primary-case-state)*

### SQL referencing this table
- [SQL-024: Retrieve case status codes by prior status](sql/SQL-024-case-status-list.md)

---

## DUAL

| Field | Value |
| --- | --- |
| **Database** | Oracle |
| **Schema/Link** | N/A (pseudo-table) |
| **Purpose** | Oracle system pseudo-table used for a scalar PL/SQL function call (assigned attorney's manager email) and to prepend a blank/"no selection" row onto several code lists via `UNION`. |
| **Access pattern** | Direct SQL |
| **CRUD operations** | Read |
| **Key columns used** | N/A (pseudo-table; no columns) |
| **Sequence** | N/A |
| **Related tables** | None |
| **Business objects** | `clsScheduleManager`, `clsOtherPartiesManager`, `clsOutcomeManager` (App classes) |

### SQL referencing this table
- [SQL-016: Retrieve attorney firm list for other parties](sql/SQL-016-other-parties-attorney-list.md)
- [SQL-018: Retrieve employer/insurer firm list for other parties](sql/SQL-018-employer-type-list.md)
- [SQL-019: Retrieve appeal hearing level list by case](sql/SQL-019-appeal-num-level-list.md)
- [SQL-020: Retrieve assigned attorney's manager email address](sql/SQL-020-get-attorney-manager-email.md)

---

## Table relationship diagram

The following diagram shows the confirmed and inferred relationships between tables used by this library's SQL.

```mermaid
erDiagram
    ADP_EMPLOYEE ||--o{ ATTORNEY_PROFILE : "EMPLID = PROFILE_EMPLID"
    LEGAL_PERSON ||--o{ LEGAL_PERSON_TYPE : "LEGAL_PERSON_SEQ_NUM join"
    APPEAL }o--|| REVIEW_LEVEL_CODE : "REVIEW_LEVEL_CODE lookup (inferred)*"
```

`OTHER_FIRM` is queried as a parallel, non-joined alternative to `LEGAL_PERSON`/`LEGAL_PERSON_TYPE` and is not shown with a relationship line. `CASE_STATUS_CODE`'s relationship to the `LEGAL_CASE` table (owned by `SAIF.Business.Claim.Legal`) is inferred from naming convention only and is not diagrammed, since `LEGAL_CASE` is not part of this library's own table set*.

> **Note:** Statements marked with `*` were inferred from code structure, naming conventions, or
> contextual evidence where direct confirmation was not available in source artifacts. Statements
> marked with `**` could not be confirmed; treat as unknown until verified.

## Related documentation
- [Index](index.md)
- [Overview](overview.md)
- [Architecture](architecture.md)
- [Dependencies](dependencies.md)
- [Operations and processes](operations-processes.md)
- [Business rules](business-rules.md)
- [SQL catalog](sql/index.md)
- [API reference](api/index.md)

## Sources
- `SAIF.Application.Claim.Legal\clsAttorneyTableManager.vb`
- `SAIF.Application.Claim.Legal\clsHearingLocationManager.vb`
- `SAIF.Application.Claim.Legal\clsLegalPersonManager.vb`
- `SAIF.Application.Claim.Legal\clsLegalData.vb`
- `SAIF.Application.Claim.Legal\LegalManagerBase.vb`
- `SAIF.Application.Claim.Legal\LegalNet\clsOtherPartiesManager.vb`
- `SAIF.Application.Claim.Legal\LegalNet\clsOutcomeManager.vb`
- `SAIF.Application.Claim.Legal\LegalNet\clsUpdateCaseManager.vb`
- `SAIF.Application.Claim.Legal\LegalNet\clsSummaryManager.vb`
- `SAIF.Application.Claim.Legal\LegalNet\clsScheduleManager.vb`